﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;//to parse the file
using System.Collections;
//using System.Data.SQLite;
using System.IO;
namespace DOF
{
    public partial class GUI : Form
    {
        private ArrayList _arrCTCIMsgs = new ArrayList();
        //private SQLiteConnection _objConnection =null;   
        private SqlConnection _objConnection =null;   
        //SQLiteTransaction _objTransaction = null;
        private SqlTransaction _objTransaction = null;
        string _currentDate = "";
        const string _cstDateFormat = "yyyy-MM-dd";
        const string _cstDateTimeFormat = "yyyy-MM-dd hh:mm:ss.fff";
        StringBuilder sbProgramLog = new StringBuilder();

        public GUI()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
            txtLogFile.Text = openFileDialog1.FileName;
        }
        private bool IsProcessTable(string sDate,string sTable)
        { 
            bool bResult = false;
            //SQLiteCommand objCommand = _objConnection.CreateCommand();
            SqlCommand objCommand = _objConnection.CreateCommand();
            objCommand.Transaction = _objTransaction;
            //SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(objCommand);
            SqlDataAdapter ObjDataAdapter = new SqlDataAdapter(objCommand);
            DataSet dsData = new DataSet();           

            objCommand.CommandText = "select * from [Log] where datediff(day,logDate,'" + sDate + "')=0 and [Object] = '" + sTable + "'";            
            ObjDataAdapter.Fill(dsData, "Log");
            if (dsData.Tables[0].Rows.Count > 0) bResult = true;        // we already process this table on proposed date.

            return bResult;
        }
        private void ProcessADate()
        {
            ///sbProgramLog.AppendLine("Reading from files")
            if (txtLogFile.Text == "" || txtLogOut.Text == "") return;
            if (IsProcessTable(_currentDate, "CTCI_1I"))
            {
                txtResult.Text += "Processed Tables(1I,2I,2E) in " + _currentDate + " \r\n";
                return;
            }
            txtResult.Text += "Reading from files \r\n";
            //string sLine = @"[2009-08-31 08:27:54,687] MSGOUT_LOG - 08:27:54.687 Seq[1] Ack[3] Lnk[11] OPId[DT]  = NumOfMsg==4|Msg[0]={1I011112 10242750011C082868CII     B2000    2000    ATO   M     C     }, Msg[1]={1I011112 10242751011C027979DIC     B10000   10000   ATO   M     C     }, Msg[2]={1I011112 10242752011C027979DIC     B10000   10000   21.3  M     C     }, Msg[3]={1I011112 10242753011C007866AGF     B4000    4000    ATO   M     C     }";
            //StringBuilder sbToFile = new StringBuilder();
            //sbToFile.Append(ParseCSVData(sLine));
            Asc2ArrString T2Strings = new Asc2ArrString();
            //Noted: read file with the DealDate created time.
            string[] arrInLog = T2Strings.Convert(txtLogFile.Text); //Read In log file and store in arrInLog
            string[] arrOutLog = T2Strings.Convert(txtLogOut.Text); //ReadOut log file and store in arrOutLog
            if (arrInLog == null || arrOutLog == null) return;
            foreach (string strTmp in arrInLog)
            {
                ConvertStringToMessage(strTmp);// Parse msgs and store MsgIN in _arrCTCIMsgs                
            }
            foreach (string strTmp in arrOutLog)
            {
                ConvertStringToMessage(strTmp);// Parse msgs and store MsgOUT in _arrCTCIMsgs                
            }
            //save to csv file.
            //string strFilePath = @"D:\temp\test.csv";
            //System.IO.File.WriteAllText(strFilePath, sbToFile.ToString());

            // save to SQlite DB            
            _objTransaction = _objConnection.BeginTransaction();
            SaveToSQLiteDB(_currentDate);   // Save all msgs in _arrCTCIMsgs to DBs with DealDate value
            _objTransaction.Commit();
            txtResult.Text += "Commit Data to 1I,2I,2E  -> Done \r\n";
            _arrCTCIMsgs.Clear();
            // Fill the deal
            FillDeals(_currentDate);
            txtResult.Text += "Fill Deal -> Done \r\n";
        }

        private void btnProcess_Click(object sender, EventArgs e)
        {
            ProcessADate();
        }
        private void SaveToSQLiteDB(string sLogDate)
        {
            //SQLiteCommand objCommand = _objConnection.CreateCommand();
            SqlCommand objCommand = _objConnection.CreateCommand();
            objCommand.Transaction = _objTransaction;
            CTCI_1I msg1I;
            CTCI_2E msg2E;
            CTCI_2I msg2I;
            
            for (int i = 0; i < _arrCTCIMsgs.Count; i++)
            {
                if (_arrCTCIMsgs[i].GetType().Name == "CTCI_1I")
                {
                    msg1I = (CTCI_1I)_arrCTCIMsgs[i];
                    objCommand.CommandText = string.Format("insert into CTCI_1I values('{0}',{1},'{2}','{3}','{4}','{5}',{6})", msg1I._OrderDate.ToString(_cstDateTimeFormat), msg1I._OrderNum, msg1I._ClientID, msg1I._Side, msg1I._Symbol, msg1I._Price, msg1I._Vol);
                }
                else if (_arrCTCIMsgs[i].GetType().Name == "CTCI_2E")
                {
                    msg2E = (CTCI_2E)_arrCTCIMsgs[i];
                    objCommand.CommandText = string.Format("insert into CTCI_2E values('{0}',{1},'{2}',{3},{4})", msg2E._OrderDate.ToString(_cstDateTimeFormat), msg2E._OrderNum, msg2E._Side, msg2E._Vol, msg2E._Price);
                }
                else if (_arrCTCIMsgs[i].GetType().Name == "CTCI_2I")
                {
                    msg2I = (CTCI_2I)_arrCTCIMsgs[i];
                    objCommand.CommandText = string.Format("insert into CTCI_2I values('{0}',{1},{2},{3},{4})", msg2I._OrderDate.ToString(_cstDateTimeFormat), msg2I._BuyOrderNum, msg2I._SellOrderNum, msg2I._Vol, msg2I._Price);
                }
                
                // Try to insert, if it has dup OSN -> irgnore.
                try
                {
                    objCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                { 
                
                }
                
            }
            // Update logs
            objCommand.CommandText = "insert into [Log] values('" + sLogDate + "','CTCI_1I','Insert new data','Parse file and store to DB')";
            objCommand.ExecuteNonQuery();
            objCommand.CommandText = "insert into [Log] values('" + sLogDate + "','CTCI_2I','Insert new data','Parse file and store to DB')";
            objCommand.ExecuteNonQuery();
            objCommand.CommandText = "insert into [Log] values('" + sLogDate + "','CTCI_2E','Insert new data','Parse file and store to DB')";
            objCommand.ExecuteNonQuery();
            
        }
        // s: a line from log [2009-08-31 08:27:54,687] MSGOUT_LOG - 08:27:54.687 Seq[1] Ack[3] Lnk[11] OPId[DT]  = NumOfMsg==4|Msg[0]={1I011112 10242750011C082868CII     B2000    2000    ATO   M     C     }, Msg[1]={1I011112 10242751011C027979DIC     B10000   10000   ATO   M     C     }, Msg[2]={1I011112 10242752011C027979DIC     B10000   10000   21.3  M     C     }, Msg[3]={1I011112 10242753011C007866AGF     B4000    4000    ATO   M     C     }
        private void ConvertStringToMessage(string s)
        {
            //s = @"[2009-08-01 08:27:54.687]";            
            Regex regexObjDateTime = new Regex(@"\[(\s*?.*?)\]"); // take the string between [<string>] - datetime
            Match matchObj = regexObjDateTime.Match(s);
            string sDateTime,sTemp;
            DateTime dtOrderDate;
            if (matchObj.Success)
            {
                sTemp = matchObj.Value;
                sDateTime = sTemp.Substring(1, sTemp.Length - 2);
                sDateTime = sDateTime.Replace(',', '.');
                dtOrderDate = DateTime.Parse(sDateTime);
                //MessageBox.Show(dtOrderDate.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));

            }
            else return ;

            Regex regexObj = new Regex(@"\{(\s*?.*?)\}"); // take the string between {<string>}
            matchObj = regexObj.Match(s);
            ArrayList arrStringMsg = new ArrayList();
            while (matchObj.Success)
            {
                arrStringMsg.Add(matchObj.Value); // Collect all msg in this line.
                matchObj = matchObj.NextMatch();
            }
            if (arrStringMsg.Count > 0)
            {
                CTCI_Msg[] arrCTCIMsg = new CTCI_Msg[arrStringMsg.Count];
                for (int i = 0; i < arrStringMsg.Count; i++)
                {
                    arrCTCIMsg[i] = CTCI_Msg.createInstance(arrStringMsg[i].ToString(),dtOrderDate);
                    if (arrCTCIMsg[i] != null) _arrCTCIMsgs.Add(arrCTCIMsg[i]);
                }

            }            
        }

        private void GUI_Load(object sender, EventArgs e)
        {
            txtLocation.Text = @"d:\tmp\dma";
            txtResult.Text = "";
            dtpDealDate.Text = DateTime.Now.Date.ToString(_cstDateFormat);
            _currentDate = DateTime.Parse(dtpDealDate.Text).ToString(_cstDateFormat);
            //sql_con = new SQLiteConnection("Data Source=DemoT.db;Version=3;New=False;Compress=True;"); 
            //_objConnection = new SQLiteConnection("Data Source=Dofdb.sqlite;");
            _objConnection = new SqlConnection("Data Source=localhost;Initial Catalog=dof;Integrated Security=True");
            _objConnection.Open();            
        }

        private void GUI_FormClosed(object sender, FormClosedEventArgs e)
        {
            _objConnection.Close();
        }

        private void FillDeals(string sDate)
        {
            //SQLiteCommand objCommand = _objConnection.CreateCommand();
            SqlCommand objCommand = _objConnection.CreateCommand();
            objCommand.Transaction = _objTransaction;
            //SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(objCommand);
            SqlDataAdapter ObjDataAdapter = new SqlDataAdapter(objCommand);
            DataSet dsCTCI_1I_2I = new DataSet();
            DataSet dsCTCI_1I_2E = new DataSet();

            //Match 1I and 2E
            objCommand.CommandText = "select i1.*,avg(e2.Price) as MatchedPrice , SUM(e2.Amount) as MatchedVol from  CTCI_1I i1 inner join CTCI_2E e2 on i1.OSN=e2.OSN where datediff(day,i1.orderDate,'" + sDate + "')=0 group by i1.OSN,i1.OrderDate,i1.Account,i1.Side,i1.Stock,i1.Price,i1.Amount ";
            ObjDataAdapter.Fill(dsCTCI_1I_2E, "CTCI_1I2E");
            ////Match 1I and 2I
            objCommand.CommandText = "select i1.*,avg(i2.Price) as MatchedPrice , SUM(i2.Amount) as MatchedVol  from  CTCI_1I i1 inner join CTCI_2I i2 on i1.OSN=i2.Buy_OSN or i1.OSN=i2.Sell_OSN where datediff(day,i1.orderDate,'" + sDate + "')=0 group by i1.OSN,i1.OrderDate,i1.Account,i1.Side,i1.Stock,i1.Price,i1.Amount ";
            ObjDataAdapter.Fill(dsCTCI_1I_2I, "CTCI_2I");            
            
            _objTransaction = _objConnection.BeginTransaction();
            objCommand.Transaction = _objTransaction;
            foreach (DataRow dr1I2E in dsCTCI_1I_2E.Tables[0].Rows)
            {
                objCommand.CommandText = string.Format("insert into HoSE_Deals values('{0}',{1},'{2}','{3}','{4}','{5}',{6},{7},{8})", ((DateTime)dr1I2E[0]).ToString(_cstDateTimeFormat), dr1I2E[1].ToString(), dr1I2E[2].ToString(), dr1I2E[3].ToString(), dr1I2E[4].ToString(), dr1I2E[5].ToString(), dr1I2E[6].ToString(), dr1I2E[7].ToString(),dr1I2E[8].ToString());
                objCommand.ExecuteNonQuery();                
            }
            foreach (DataRow dr1I2I in dsCTCI_1I_2I.Tables[0].Rows)
            {
                objCommand.CommandText = string.Format("insert into HoSE_Deals values('{0}',{1},'{2}','{3}','{4}','{5}',{6},{7},{8})", ((DateTime)dr1I2I[0]).ToString(_cstDateTimeFormat), dr1I2I[1].ToString(), dr1I2I[2].ToString(), dr1I2I[3].ToString(), dr1I2I[4].ToString(), dr1I2I[5].ToString(), dr1I2I[6].ToString(), dr1I2I[7].ToString(), dr1I2I[8].ToString());
                objCommand.ExecuteNonQuery();
            }

            // Update log
            objCommand.CommandText = "insert into [Log] values('" + sDate + "','HoSE_Deals','Make deal','Match orders')";
            objCommand.ExecuteNonQuery();

            _objTransaction.Commit();            
        }       
        private void btnBrowseOutFile_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
            txtLogOut.Text = openFileDialog1.FileName;
        }

        private void CleanTable(string sLogDate, string sTableName)
        {
            string sHitoryTable = sTableName + "_HISTORY";
            if (IsProcessTable(sLogDate, sHitoryTable))
            {
                txtResult.Text += "Processed Table "+sTableName+" in " + _currentDate + " \r\n";
                return;
            }

            //SQLiteCommand objCommand = _objConnection.CreateCommand();
            SqlCommand objCommand = _objConnection.CreateCommand();
            _objTransaction = _objConnection.BeginTransaction();
            objCommand.Transaction = _objTransaction;
            // Move data from CTCI_1I to CTCI_1I_HISTORY            
            objCommand.CommandText = "insert into " + sHitoryTable + " select * from " + sTableName;
            objCommand.ExecuteNonQuery();
            // Update Log
            objCommand.CommandText = "insert into [Log] values('" + sLogDate + "','" + sHitoryTable + "','Move Data','Copy data from " + sTableName + "')";
            objCommand.ExecuteNonQuery();
            // Clean CTCI_1I
            objCommand.CommandText = "delete from " + sTableName;
            objCommand.ExecuteNonQuery();
            // Update Log
            objCommand.CommandText = "insert into [Log] values('" + sLogDate + "','" + sTableName + "','Clear Data','lear data for the new process')";
            objCommand.ExecuteNonQuery();
            _objTransaction.Commit();
            txtResult.Text += "Copy data from "+sTableName+" -> "+sHitoryTable+" and clean data on "+sTableName+" \r\n";
        }

        private void DoHouseWork(string sDate)
        {
            CleanTable(sDate, "CTCI_1I");            
            CleanTable(sDate, "CTCI_2I");            
            CleanTable(sDate, "CTCI_2E");            
            CleanTable(sDate, "HoSE_Deals");            
        }

      

        private void btnCheck_Click(object sender, EventArgs e)
        {
            //string[] arrFilePaths = Directory.GetFiles(@"++", "*.log",SearchOption.AllDirectories);           
            string[] arrFilePaths = Directory.GetFiles(txtLocation.Text, "dma*.log");            
            DateTime dtSearchDate = new DateTime();
            dtSearchDate = DateTime.Parse(_currentDate);
            int iCount = 0;
            foreach (string sFile in arrFilePaths)
            {                
                if (File.GetLastWriteTime(sFile).Date.Equals(dtSearchDate.Date))
                {
                    //arrSearchedFiles.Add(sFile);
                    if (sFile.IndexOf("dmaIn")>=0)
                    {
                        txtLogFile.Text = sFile;
                    }
                    else if (sFile.IndexOf("dmaOut")>=0)
                    {
                        txtLogOut.Text = sFile;
                    }
                    iCount++;
                    txtResult.Text += sFile + " -> " + File.GetLastWriteTime(sFile).Date.ToString(_cstDateFormat) + "\r\n";
                }
            }
            txtResult.Text += "Found files : " + iCount.ToString() + "\r\n";

        }

        private void btnFolderBrowse_Click(object sender, EventArgs e)
        {
            folderBrowserDialog1.SelectedPath = @"e:\TNT 2009\Finance\HoSE\Info\HSC info\dt\All";
            folderBrowserDialog1.ShowDialog();
            txtLocation.Text = folderBrowserDialog1.SelectedPath;
        }

        private void dtpDealDate_ValueChanged(object sender, EventArgs e)
        {
            _currentDate = DateTime.Parse(dtpDealDate.Text).ToString(_cstDateFormat);
        }

        private void btnHouseWork_Click(object sender, EventArgs e)
        {
            DoHouseWork(_currentDate);
            //txtResult.Text += "Do House work -> Done \r\n";
        }

        private void CleanData()
        {
            txtLogFile.Text = txtLogOut.Text = ""; // restart for a day.
            _arrCTCIMsgs.Clear();
        }
        private void btnRunFolder_Click(object sender, EventArgs e)
        {
            string[] arrFilePaths = Directory.GetFiles(txtLocation.Text, "dma*.log");            
            DateTime dtMinDate = new DateTime();
            DateTime dtMaxDate = new DateTime();
            DateTime dtCurDateFile;
            int i=0;
            foreach (string sFile in arrFilePaths)
            { 
                dtCurDateFile = File.GetLastWriteTime(sFile);
                if (i==0)
                {
                    //dtMinDate = DateTime.pa
                    dtMinDate = dtMaxDate= dtCurDateFile;
                    i = 1;
                }
                if (dtCurDateFile.CompareTo(dtMinDate) < 0) dtMinDate = dtCurDateFile;
                if (dtCurDateFile.CompareTo(dtMaxDate) > 0) dtMaxDate = dtCurDateFile;
            }
            if (i > 0)
            { // Process the whole folder.
                dtCurDateFile = dtMinDate;
                while(dtCurDateFile.CompareTo(dtMaxDate)<=0)
                {
                    CleanData();
                    dtpDealDate.Text = dtCurDateFile.ToString(_cstDateFormat);
                    txtResult.Text += "Process date: " + _currentDate + "\r\n";                    
                    btnCheck_Click(sender, e);// Process a day.
                    ProcessADate();
                    DoHouseWork(_currentDate);

                    dtCurDateFile= dtCurDateFile.AddDays(1); //Move to next date.
                }
                
            }
            //MessageBox.Show(dtMinDate.ToString("yyyy-MM-dd") + " - " + dtMaxDate.ToString("yyyy-MM-dd"));
        }
    }
}
